﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapEKVRECH(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveEKVRECH(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryEKVRECH(dwData, 1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapEKVRECH(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertEKVRECH(dwLocal);
            }
            UpdateEKVRECH(dwLocal);
        }
        public void InsertEKVRECH(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "INSERT INTO EKVRECH(EKVMANDANT, EKVABLAGE, EKVBELEG, EKVLIEFNR, EKVRECHNR, EKVBETRAG, EKVWAEHRSL, EKVKST, EKVEMPF, EKVKIR, EKVABRECHKZ, EKVFILLER, EKVAENDZL, EKVAENDBEN, EKVAENDDAT, EKVNEUBEN, EKVNEUDAT)"
            + " VALUES (:EKVMANDANT, :EKVABLAGE, :EKVBELEG, '00000003', 'WP237-1   ', 23400, 'RMB', 0, 'BATCH   ', ' ', ' ', '                       ', 1, 'INTEXE  ', 20140304, 'INTEXE  ', 20140304)";
            or1.Parameters.Add("EKVMANDANT", OracleType.NVarChar).Value = dwData["EKVMANDANT"].ToString();
            or1.Parameters.Add("EKVABLAGE", OracleType.NVarChar).Value = dwData["EKVABLAGE"].ToString();
            or1.Parameters.Add("EKVBELEG", OracleType.NVarChar).Value = dwData["EKVBELEG"].ToString();
            Connect2Proxy(or1);
        }
        public void UpdateEKVRECH(DataRow dwData, int option = 1, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region===================
                    or1.CommandText = "UPDATE EKVRECH SET "
                        + "EKVMANDANT=:EKVMANDANT,"
                        + "EKVABLAGE=:EKVABLAGE,"
                        + "EKVBELEG=:EKVBELEG,"
                        + "EKVLIEFNR=:EKVLIEFNR,"
                        + "EKVRECHNR=:EKVRECHNR,"
                        + "EKVBETRAG=:EKVBETRAG,"
                        + "EKVWAEHRSL=:EKVWAEHRSL,"
                        + "EKVKST=:EKVKST,"
                        + "EKVEMPF=:EKVEMPF,"
                        + "EKVKIR=:EKVKIR,"
                        + "EKVABRECHKZ=:EKVABRECHKZ,"
                        + "EKVFILLER=:EKVFILLER,"
                        + "EKVAENDZL=:EKVAENDZL,"
                        + "EKVAENDBEN=:EKVAENDBEN,"
                        + "EKVAENDDAT=:EKVAENDDAT,"
                        + "EKVNEUBEN=:EKVNEUBEN,"
                        + "EKVNEUDAT=:EKVNEUDAT "
                        + " WHERE  "
                        + "  EKVMANDANT=:EKVMANDANT AND EKVABLAGE=:EKVABLAGE AND EKVBELEG=:EKVBELEG ";

                    or1.Parameters.Add("EKVMANDANT", OracleType.NVarChar).Value = dwData["EKVMANDANT"].ToString();
                    or1.Parameters.Add("EKVABLAGE", OracleType.NVarChar).Value = dwData["EKVABLAGE"].ToString();
                    or1.Parameters.Add("EKVBELEG", OracleType.NVarChar).Value = dwData["EKVBELEG"].ToString();
                    or1.Parameters.Add("EKVLIEFNR", OracleType.NVarChar).Value = dwData["EKVLIEFNR"].ToString();
                    or1.Parameters.Add("EKVRECHNR", OracleType.NVarChar).Value = dwData["EKVRECHNR"].ToString();
                    or1.Parameters.Add("EKVBETRAG", OracleType.NVarChar).Value = dwData["EKVBETRAG"].ToString();
                    or1.Parameters.Add("EKVWAEHRSL", OracleType.NVarChar).Value = dwData["EKVWAEHRSL"].ToString();
                    or1.Parameters.Add("EKVKST", OracleType.NVarChar).Value = dwData["EKVKST"].ToString();
                    or1.Parameters.Add("EKVEMPF", OracleType.NVarChar).Value = dwData["EKVEMPF"].ToString();
                    or1.Parameters.Add("EKVKIR", OracleType.NVarChar).Value = dwData["EKVKIR"].ToString();
                    or1.Parameters.Add("EKVABRECHKZ", OracleType.NVarChar).Value = dwData["EKVABRECHKZ"].ToString();
                    or1.Parameters.Add("EKVFILLER", OracleType.NVarChar).Value = dwData["EKVFILLER"].ToString();
                    or1.Parameters.Add("EKVAENDZL", OracleType.NVarChar).Value = dwData["EKVAENDZL"].ToString();
                    or1.Parameters.Add("EKVAENDBEN", OracleType.NVarChar).Value = dwData["EKVAENDBEN"].ToString();
                    or1.Parameters.Add("EKVAENDDAT", OracleType.NVarChar).Value = dwData["EKVAENDDAT"].ToString();
                    or1.Parameters.Add("EKVNEUBEN", OracleType.NVarChar).Value = dwData["EKVNEUBEN"].ToString();
                    or1.Parameters.Add("EKVNEUDAT", OracleType.NVarChar).Value = dwData["EKVNEUDAT"].ToString();
                    #endregion
                    break;
                case 2:

                    break;
                case 21:

                    break;

            }
            Connect2Proxy(or1);
        }
        public DataSet QueryEKVRECH(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM EKVRECH WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM EKVRECH WHERE EKVMANDANT=:EKVMANDANT  AND EKVABLAGE=:EKVABLAGE AND EKVBELEG=:EKVBELEG";
                    or1.Parameters.Add("EKVMANDANT", OracleType.NVarChar).Value = dwData["EKVMANDANT"].ToString();
                    or1.Parameters.Add("EKVABLAGE", OracleType.NVarChar).Value = dwData["EKVABLAGE"].ToString();
                    or1.Parameters.Add("EKVBELEG", OracleType.NVarChar).Value = dwData["EKVBELEG"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM EKVRECH  "
                        + " WHERE EKVMANDANT=:EKVMANDANT  AND EKVRECHNR LIKE '" + dwData["EKVRECHNR"].ToString().Trim() + "%'";
                    or1.Parameters.Add("EKVMANDANT", OracleType.NVarChar).Value = dwData["EKVMANDANT"].ToString();
                    break;
                case 3:
                    or1.CommandText = "SELECT COUNT(1) AS AA FROM EKVRECH  "
                     + " WHERE EKVMANDANT=:EKVMANDANT  AND EKVFILLER =:EKVFILLER";
                    or1.Parameters.Add("EKVMANDANT", OracleType.NVarChar).Value = dwData["EKVMANDANT"].ToString();
                    or1.Parameters.Add("EKVFILLER", OracleType.NVarChar).Value = dwData["EKVFILLER"].ToString();
                    break;
                case 4:
                    or1.CommandText = "SELECT DISTINCT L.LSLIEFNA AS SELLER,L.LSNAME1 AS SELLERNAME, X.LSLIEFNA AS EXFTY,X.LSNAME1 AS FACNAME,ABAAUFKRS||ABAAUFLFD AS EKAUF,ABAVKAUFKRS||ABAVKAUFLFD AS PRODAUF,EKVABLAGE,EKVBELEG,EKVKIR,EKVAENDBEN,EKVAENDDAT,P.ABPLIEFNR,FSSZUSCHN,'      ' AS MATTYP,MTYPE,CASE WHEN  WAUEBM='KG' THEN ROUND(ABARESMG*WAFGRLFD*0.001,1) ELSE ABARESMG END AS RQTY,WAUEBM,0 AS ACTUALQTY,TRIM(ABAART)||'-'||TRIM(ABAFARBE) AS MAT,ARTDES,CDES,M.ABPART,FSABEZ,EXART,EXCOLOR,EKPPRME*0.001 AS EKPPR,'RMB/'||TRIM(EKPMEEH) AS PUM,CASE  WHEN WAUEBM='KG' THEN ROUND(ABARESMG*WAFGRLFD*0.001*EKPPRME*0.001,0) ELSE ROUND(ABARESMG*EKPPRME*0.001,0) END AS EKPVALUE,EKVBETRAG*0.01 AS PAID,EKVEMPF FROM EKVRECH P JOIN ICC_ABAUFM M ON TRIM(EKVFILLER)=TRIM(ABAAUFKRS||'-'||ABAAUFLFD||'-'||ABAAUFPOS) JOIN EKPOS ON EKPAUFKRS=ABAAUFKRS AND EKPAUFLFD=ABAAUFLFD AND EKPAUFPOS=ABAAUFPOS JOIN ABPOS P ON ABPAUFKRS=ABAVKAUFKRS AND ABPAUFLFD=ABAVKAUFLFD AND ABPAUFPOS=ABAVKAUFPOS JOIN LIEFDAT L ON L.LSLIEFNR=EKVLIEFNR LEFT JOIN LIEFDAT X ON ABPLIEFNR=X.LSLIEFNR"
                        + " WHERE EKVAENDDAT LIKE '" + dwData["EKVAENDDAT"].ToString() + "%'";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "EKVRECH";
            Return = Result;
            return Return;
        }
    }
}
